--    Author    : ChenErHao
--    Name      : DM.T_MTH_PANDECT_LOAN_BALANCE.HQL
--    Functions : 总览-贷款余额
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-24  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_PANDECT_LOAN_BALANCE PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#')
SELECT
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.PRODUCT_CATEGORY,
    T1.CUSTOMER_TYPE,
    T1.LPIC,
    T1.ENTERPRISE_SCALE,
    '' REGIST_ADD_CODE,
    nvl(sum(case when DATA_DATE='#V_DATA_DATE#' then T1.LOAN_BALANCE end),0) LOAN_BALANCE,
    nvl(sum(case when DATA_DATE='#V_LAST_1M_END_DATE#' then T1.LOAN_BALANCE end),0) LAST_MTH_LOAN_BALANCE,
    nvl(sum(case when DATA_DATE='#V_LAST_12M_END_DATE#' then T1.LOAN_BALANCE end),0) LAST_YEAR_LOAN_BALANCE,
    nvl(sum(case when DATA_DATE='#V_DATA_DATE#' then T1.WEIGHT_AMT end),0)   WEIGHT_AMT,
    nvl(sum(case when DATA_DATE='#V_LAST_1M_END_DATE#' then T1.WEIGHT_AMT end),0)  LAST_MTH_WEIGHT_AMT,
    nvl(sum(case when DATA_DATE='#V_LAST_12M_END_DATE#' then T1.WEIGHT_AMT end),0)  LAST_YEAR_WEIGHT_AMT
FROM
    (
        SELECT  --第二层机构统计
            DATA_DATE,
            BELONG_AREA_CODE   AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_2='' THEN '9999'
                 ELSE B.ORG_LVL_ID_2 END   ORG_ID,
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            CUSTOMER_TYPE         CUSTOMER_TYPE,--客户类型
            NVL(LPIC_SUB,'9999') LPIC,-- 企业分行业
            nvl(ENTERPRISE_SCALE ,'CS05')         ENTERPRISE_SCALE,--企业规模 
            SUM(LOAN_BALANCE)      LOAN_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
            (SELECT DATA_DATE,CUSTOMER_TYPE,ENTERPRISE_SCALE, BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,
            LOAN_BALANCE,LOAN_OCCURRENCE_LEVEL
            FROM EDW.LOAB   
        WHERE
            DATA_DATE in ('#V_DATA_DATE#' ,'#V_LAST_1M_END_DATE#' ,'#V_LAST_12M_END_DATE#')) A 
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE END =B.ORG_ID
        LEFT JOIN DMCODE.t_loan_pro_type C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY END  =C.loan_pro_id
        GROUP BY
            DATA_DATE ,
            A.BELONG_AREA_CODE,
            B.ORG_LVL_ID_2,
            C.loan_pro_id_1,
            CUSTOMER_TYPE,
            LPIC_SUB,
            ENTERPRISE_SCALE
            
        union all
        
        SELECT  --第三层机构统计
            DATA_DATE,
            BELONG_AREA_CODE   AREA_NO_ID, 
            CASE WHEN B.ORG_LVL_ID_3 IS NULL THEN '9999'
                 WHEN B.ORG_LVL_ID_3='' THEN '9999'
                 ELSE B.ORG_LVL_ID_3 END   ORG_ID,
            CASE WHEN C.loan_pro_id_1 IS NULL THEN '9999'
                 WHEN C.loan_pro_id_1 ='' THEN '9999'
                 ELSE C.loan_pro_id_1 END PRODUCT_CATEGORY,--贷款产品类别
            CUSTOMER_TYPE         CUSTOMER_TYPE,--客户类型
            NVL(LPIC_SUB,'9999') LPIC,-- 企业分行业
            nvl(ENTERPRISE_SCALE ,'CS05')         ENTERPRISE_SCALE,--企业规模 
            SUM(LOAN_BALANCE)      LOAN_BALANCE,
            SUM(LOAN_OCCURRENCE_LEVEL)  WEIGHT_AMT
        FROM
            (SELECT DATA_DATE,CUSTOMER_TYPE,ENTERPRISE_SCALE, BELONG_AREA_CODE,DATA_ORG_CODE,PRODUCT_CATEGORY,LPIC_SUB,
            LOAN_BALANCE,LOAN_OCCURRENCE_LEVEL
            FROM EDW.LOAB   
        WHERE
            DATA_DATE in ('#V_DATA_DATE#' ,'#V_LAST_1M_END_DATE#' ,'#V_LAST_12M_END_DATE#')) A 
        LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE END =B.ORG_ID
        LEFT JOIN DMCODE.t_loan_pro_type C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY END  =C.loan_pro_id
        GROUP BY
            DATA_DATE ,
            A.BELONG_AREA_CODE,
            B.ORG_LVL_ID_3,
            C.loan_pro_id_1,
            CUSTOMER_TYPE,
            LPIC_SUB,
            ENTERPRISE_SCALE
        ) T1
   GROUP BY 
    T1.AREA_NO_ID,
    T1.ORG_ID,
    T1.PRODUCT_CATEGORY,
    T1.CUSTOMER_TYPE,
    T1.LPIC,
    T1.ENTERPRISE_SCALE 
;